# -*- coding: utf-8 -*-
"""
Created on Mon Nov 25 21:24:00 2024

@author: SGMWCQPT01
"""

import os
import json
import time
import datetime
import requests
import logging
import pandas as pd
from functools import partial
from sqlalchemy import create_engine, MetaData, Table, select, func, cast, DateTime, and_
from sqlalchemy.orm import sessionmaker  # 新增postgresql数据库连接
import numpy as np
import matplotlib.pyplot as plt
from matplotlib.font_manager import FontProperties
from scipy.fftpack import fft, ifft
import seaborn as sns

# 设置中文字体支持
plt.rcParams['font.sans-serif'] = ['SimSun']
plt.rcParams['axes.unicode_minus'] = False

# postgresql数据库连接信息
user = 'postgres'            # 用户
pwd = 'cqpt730'              # 数据库密码
address = '10.64.35.32'      # 主机名或IP地址
port = '5432'                # 端口号
database = 'pmc'             # 数据库名称

# 创建数据库引擎
postgresql1 = f'postgresql+psycopg2://{user}:{pwd}@{address}:{port}/{database}'
engine = create_engine(postgresql1)
# 反射表结构
metadata = MetaData()
table = Table('fanuc_cycle', metadata, autoload_with=engine)

# 将timestamp列转换为日期时间类型
time_cast = cast(table.c.timestamp, DateTime)
# 定义时间范围(完整循环vs几个循环）
start_time ='2024-11-28 12:03:00' #'2024-11-22 10:00:00'   #200件  '2024-11-29 12:01:31'
end_time ='2024-11-29 12:01:31' #'2024-11-22 19:30:00'

# 定义要提取的负载列
load_columns = ['spindle_load', 'load_x', 'load_y', 'load_z', 'load_b']



# 定义函数来提取和处理数据(最大值,最小，均值）
def extract_and_process_data(tool_number, load_columns, start_time, end_time):
    # 将 tool_number 转换为字符串
    tool_number_str = str(tool_number)
    
    # 构建查询：按秒级时间分组，获取每个组的最大值////mean均值  min
    subquery = (select([
        func.to_char(time_cast, 'YYYY-MM-DD HH24:MI:SS.US').label('time_ms'),
        *[func.avg(getattr(table.c, col)).label(f'avg_{col.lower()}') for col in load_columns]
    ])
                .where(and_(time_cast >= start_time, time_cast < end_time, table.c.toolnum == tool_number_str))
                .group_by(func.to_char(time_cast, 'YYYY-MM-DD HH24:MI:SS.US'))
                .alias())

    # 构建主查询：连接原表和子查询，获取每个唯一秒时间对应的最大值的行
    query = (select([table])
             .select_from(table.join(subquery,
                                     (func.to_char(time_cast, 'YYYY-MM-DD HH24:MI:SS.US') == subquery.c.time_ms) & 
                                     and_(*[getattr(table.c, col) == getattr(subquery.c, f'avg_{col.lower()}') for col in load_columns])))
             .where(and_(time_cast >= start_time, time_cast < end_time, table.c.toolnum == tool_number_str)))

    # 执行查询
    with engine.connect() as connection:
        result = connection.execute(query)
        data = [dict(row) for row in result]

    # 转换为DataFrame
    df = pd.DataFrame(data)
    df['timestamp'] = pd.to_datetime(df['timestamp']) #数据转换成datetime类型，方便进行时间序列分析
    df['time_diff_seconds'] = df['timestamp'].diff().dt.total_seconds() #diff()计算每行的时间戳与其上一行时间戳之间的差异,最将时间差转换为总秒数
    
    # 分离数据
    load_data = {}
    for col in load_columns:
        load_data[col] = df[[col, 'timestamp', 'time_diff_seconds']]  # 数据类，时间戳，时间间隔值

    return load_data
# ###################均值
def extract_and_process_data0(tool_number, load_columns, start_time, end_time):
    tool_number_str = str(tool_number)
    
    # 构建查询以获取每个 toollife 下 load_columns 的平均值
    subqueries = [
        func.avg(getattr(table.c, col)).label(col) for col in load_columns
    ]
    
    query = (
        select([
            table.c.toollife,
            *subqueries
        ])
        .where(and_(table.c.timestamp >= start_time, table.c.timestamp < end_time, table.c.toolnum == tool_number_str))
        .group_by(table.c.toollife)
        .order_by(table.c.toollife)
    )

    # 执行查询
    with engine.connect() as connection:
        result = connection.execute(query)
        data = [dict(row) for row in result]

    # 转换为DataFrame
    df = pd.DataFrame(data)
    df['toollife'] = pd.to_numeric(df['toollife'], errors='coerce')
    
    # 删除含有 NaN 值的行
    df = df.dropna(subset=['toollife'])

    return df
###########
########
# 定义函数来计算FFT和频率轴
def compute_fft_and_freq(data_series, time_diff_seconds):
    """计算FFT和频率轴"""
    N = len(data_series)
    yf = fft(data_series - np.mean(data_series)) / N  # 归一化并去除直流分量
    xf = np.linspace(0.0, 1.0 / (2.0 * time_diff_seconds), N // 2)  # 频率轴计算
    return yf[:N//2], xf  # 返回一半的FFT结果和频率轴

# 定义函数来绘制频谱图
def plot_spectrum(xf_half, amplitude_spectrums, titles, colors, save_paths=None, show_legend=True):
    """绘制并保存频谱图，并输出统计指标"""
    plt.figure(figsize=(14, 8))
    
    # 初始化一个空列表用于存储所有频谱的数据
    all_data = []
    
    for spectrum, color, title in zip(amplitude_spectrums, colors, titles):
        # 使用Seaborn绘制频谱图
        sns.lineplot(x=xf_half * 1000, y=np.abs(spectrum), color=color, label=title)
        # 将当前频谱的绝对值添加到 all_data 列表中
        all_data.append(np.abs(spectrum))
    
    # 添加标签
    xlabel_text = ', '.join(titles)
    plt.xlabel(f'频率 (Hz) - {xlabel_text}', fontsize=14)
    plt.ylabel('振幅', fontsize=14)
    
    # 如果需要显示图例，则添加图例
    if show_legend:
        plt.legend()
    
    plt.tight_layout()
    
    # 保存图像
    if save_paths:
        for path in save_paths:
            plt.savefig(path, format='pdf', bbox_inches='tight')
    
    # 显示图形
    plt.show()
    
    # 计算和输出统计指标
    for spectrum, title in zip(all_data, titles):
        mean_val = np.mean(spectrum)
        std_val = np.std(spectrum)
        max_val = np.max(spectrum)
        min_val = np.min(spectrum)
        
        print(f"统计数据 - {title}:")
        print(f"  均值: {mean_val:.2f}")
        print(f"  标准差: {std_val:.2f}")
        print(f"  最大值: {max_val:.2f}")
        print(f"  最小值: {min_val:.2f}")
        print("-" * 30)

# 定义主函数来执行整个流程
def main(tool_number, start_time, end_time, load_columns):
    # 提取和处理数据
    load_data = extract_and_process_data(tool_number, load_columns, start_time, end_time)

    # 计算FFT和频率轴
    amplitude_spectrums = []
    xf_haves = []
    for col in load_columns:
        vibration_data = load_data[col][col].to_numpy()
        print('0000:\n',vibration_data)
        time_diff_seconds = load_data[col]['time_diff_seconds'].mean()   # 负载列的时间间隔的平均值
        print('time_diff_seconds:\n',time_diff_seconds)
        amplitude_spectrum, xf_half = compute_fft_and_freq(vibration_data, time_diff_seconds)
        amplitude_spectrums.append(amplitude_spectrum)
        xf_haves.append(xf_half)

    # 绘制单个频谱图
    for col, amplitude_spectrum, xf_half in zip(load_columns, amplitude_spectrums, xf_haves):
        plot_spectrum(xf_half, [amplitude_spectrum], [col], ['black'], [f'{col.lower()}.pdf'], show_legend=False)
    # 绘制对比频谱图
    # plot_spectrum(xf_haves[0], amplitude_spectrums, load_columns, ['grey', 'black', 'red', 'green'], show_legend=True)
# 定义主函数0-1 avg max2 min3      data=df
def main1(tool_number, start_time, end_time, load_columns):
    # 提取和处理数据
    df = extract_and_process_data0(tool_number, load_columns, start_time, end_time)
    # 使用Seaborn绘制叠加图
    plt.figure(figsize=(14, 8))
    for col in load_columns:
        if col != 'toollife':
            sns.lineplot(x='toollife', y=col, data=df, label=col, marker='o')

    plt.title(f'刀具 {tool_number} 加工轴负载')
    plt.xlabel('加工数')
    plt.ylabel('负载值')
    plt.legend()
    plt.show()
# 调用主函数
if __name__ == "__main__":
    tool_number = 22203
    #s输出每把刀具的频谱及统计数据
    main(tool_number, start_time, end_time, load_columns)
    #输出刀具的负载表现
    main1(tool_number, start_time, end_time, load_columns)